
// =================================================================== 
// class description : 
// =================================================================== 
// File: CompanyDBHelper.cs
// Copyright  @ AC
// History:
// 		2012/7/25  created by 
// 
// =================================================================== 

using System;
using System.Text;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using InternalManagementSystem.DAO.Models;

namespace InternalManagementSystem.DAO
{
    public class CompanyDAO : DataAccessObject
    {
        /// <summary>
        ///Description   :gets a Company record from db
        ///				  package , should be implicly downcast
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : Company object. Will be null if record not found.
        /// </summary>
        public Company LoadCompany(System.Int32 companyID)
        {
            string sqlSelect = "SELECT  CompanyID,  CompanyName,  Address,  Contact,  ContactPerson,  Phone,  Email,  StatisticalEndDate,  StatisticalStartDate,  CurrentIntegral,  IsAutoRecharge,  GuardLine  FROM Company WHERE  CompanyID = @CompanyID ";
            SqlConnection conn = new SqlConnection(ConnectionString);
            Company pCompany = null;

            //SqlCommand sqlCmd = new SqlCommand("usSelectCompany");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = companyID;
            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and close the connection. Should be <=1 results.
                if (dr.Read())
                {
                    pCompany = new Company();
                    pCompany.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    pCompany.CompanyName = (dr["CompanyName"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["CompanyName"]);
                    pCompany.Address = (dr["Address"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Address"]);
                    pCompany.Contact = (dr["Contact"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Contact"]);
                    pCompany.ContactPerson = (dr["ContactPerson"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["ContactPerson"]);
                    pCompany.Phone = (dr["Phone"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Phone"]);
                    pCompany.Email = (dr["Email"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Email"]);
                    pCompany.StatisticalEndDate = (dr["StatisticalEndDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["StatisticalEndDate"]);
                    pCompany.StatisticalStartDate = (dr["StatisticalStartDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["StatisticalStartDate"]);
                    pCompany.CurrentIntegral = (dr["CurrentIntegral"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CurrentIntegral"]);
                    pCompany.IsAutoRecharge = (dr["IsAutoRecharge"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoRecharge"]);
                    pCompany.GuardLine = (dr["GuardLine"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["GuardLine"]);
                }

                conn.Close();

            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pCompany;
        }

        /// <summary>
        ///Description   :gets a Company collection object of  all
        ///				  Company records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : Company collection object.
        /// </summary>
        public CompanyCollection LoadAllCompanys()
        {
            string sqlSelect = "SELECT  CompanyID,  CompanyName,  Address,  Contact,  ContactPerson,  Phone,  Email,  StatisticalEndDate,  StatisticalStartDate,  CurrentIntegral,  IsAutoRecharge,  GuardLine  FROM Company";
            SqlConnection conn = new SqlConnection(ConnectionString);
            CompanyCollection pCompanyCollection = new CompanyCollection();

            //SqlCommand sqlCmd = new SqlCommand("usSelectCompanyAll");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and add to collection and close the connection.
                while (dr.Read())
                {
                    Company vo = new Company();
                    vo.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    vo.CompanyName = (dr["CompanyName"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["CompanyName"]);
                    vo.Address = (dr["Address"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Address"]);
                    vo.Contact = (dr["Contact"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Contact"]);
                    vo.ContactPerson = (dr["ContactPerson"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["ContactPerson"]);
                    vo.Phone = (dr["Phone"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Phone"]);
                    vo.Email = (dr["Email"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Email"]);
                    vo.StatisticalEndDate = (dr["StatisticalEndDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["StatisticalEndDate"]);
                    vo.StatisticalStartDate = (dr["StatisticalStartDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["StatisticalStartDate"]);
                    vo.CurrentIntegral = (dr["CurrentIntegral"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CurrentIntegral"]);
                    vo.IsAutoRecharge = (dr["IsAutoRecharge"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoRecharge"]);
                    vo.GuardLine = (dr["GuardLine"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["GuardLine"]);

                    pCompanyCollection.Add(vo);
                }

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pCompanyCollection;
        }

        /// <summary>
        ///Description   :gets a Company collection object of  all
        ///				  Company records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : Company collection object.
        /// </summary>
        public CompanyCollection LoadAllCompanys(string filter, string sort)
        {
            string sqlSelect = "SELECT  CompanyID,  CompanyName,  Address,  Contact,  ContactPerson,  Phone,  Email,  StatisticalEndDate,  StatisticalStartDate,  CurrentIntegral,  IsAutoRecharge,  GuardLine  FROM Company ";
            if (!string.IsNullOrEmpty(filter))
            {
                sqlSelect += " WHERE " + filter;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sqlSelect += " order by " + sort;
            }
            SqlConnection conn = new SqlConnection(ConnectionString);
            CompanyCollection pCompanyCollection = new CompanyCollection();

            //SqlCommand sqlCmd = new SqlCommand("usSelectCompanyDynamic");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            //sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
            //sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();

                // output the results and add to collection and close the connection.
                while (dr.Read())
                {
                    Company vo = new Company();
                    vo.CompanyID = (dr["CompanyID"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CompanyID"]);
                    vo.CompanyName = (dr["CompanyName"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["CompanyName"]);
                    vo.Address = (dr["Address"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Address"]);
                    vo.Contact = (dr["Contact"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Contact"]);
                    vo.ContactPerson = (dr["ContactPerson"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["ContactPerson"]);
                    vo.Phone = (dr["Phone"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Phone"]);
                    vo.Email = (dr["Email"] == DBNull.Value) ? string.Empty : Convert.ToString(dr["Email"]);
                    vo.StatisticalEndDate = (dr["StatisticalEndDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["StatisticalEndDate"]);
                    vo.StatisticalStartDate = (dr["StatisticalStartDate"] == DBNull.Value) ? DateTime.MinValue : Convert.ToDateTime(dr["StatisticalStartDate"]);
                    vo.CurrentIntegral = (dr["CurrentIntegral"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["CurrentIntegral"]);
                    vo.IsAutoRecharge = (dr["IsAutoRecharge"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["IsAutoRecharge"]);
                    vo.GuardLine = (dr["GuardLine"] == DBNull.Value) ? 0 : Convert.ToInt32(dr["GuardLine"]);

                    pCompanyCollection.Add(vo);
                }

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return pCompanyCollection;
        }

        /// <summary>
        ///Description   :gets a DataSet collection object of  all
        ///				  Company records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : DataSet object.
        /// </summary>
        public DataSet GetDataSet()
        {
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            string sqlSelect = "SELECT  CompanyID,  CompanyName,  Address,  Contact,  ContactPerson,  Phone,  Email,  StatisticalEndDate,  StatisticalStartDate,  CurrentIntegral,  IsAutoRecharge,  GuardLine  FROM Company";
            SqlConnection conn = new SqlConnection(ConnectionString);

            //SqlCommand sqlCmd = new SqlCommand("usSelectCompanyAll");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            try
            {
                conn.Open();

                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }

        /// <summary>
        ///Description   :gets a DataSet collection object of  all
        ///				  Company records in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : DataSet object.
        /// </summary>
        public DataSet GetDataSet(string filter, string sort)
        {
            DataSet ds = null;
            SqlDataAdapter xAdapter = null;

            string sqlSelect = "SELECT  CompanyID,  CompanyName,  Address,  Contact,  ContactPerson,  Phone,  Email,  StatisticalEndDate,  StatisticalStartDate,  CurrentIntegral,  IsAutoRecharge,  GuardLine  FROM Company ";
            if (!string.IsNullOrEmpty(filter))
            {
                sqlSelect += " WHERE " + filter;
            }
            if (!string.IsNullOrEmpty(sort))
            {
                sqlSelect += " order by " + sort;
            }
            SqlConnection conn = new SqlConnection(ConnectionString);

            //SqlCommand sqlCmd = new SqlCommand("usSelectCompanyDynamic");
            SqlCommand sqlCmd = new SqlCommand(sqlSelect);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            //sqlCmd.Parameters.Add("@WhereCondition",SqlDbType.NVarChar).Value = filter;
            //sqlCmd.Parameters.Add("@OrderByExpression",SqlDbType.NVarChar).Value = sort;

            try
            {
                conn.Open();

                xAdapter = new SqlDataAdapter();
                xAdapter.SelectCommand = sqlCmd;
                ds = new DataSet();
                xAdapter.Fill(ds, "Table");
                xAdapter.Dispose();
                sqlCmd.Dispose();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }

            return ds;
        }

        /// <summary>
        ///Description   :updates a Company record in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : true - if record was update .
        //				   false - if record was not updated;
        /// </summary>
        public void UpdateCompany(Company pCompany)
        {
            string sqlUpdate = "UPDATE Company SET  CompanyName = @CompanyName, Address = @Address, Contact = @Contact, ContactPerson = @ContactPerson, Phone = @Phone, Email = @Email, StatisticalEndDate = @StatisticalEndDate, StatisticalStartDate = @StatisticalStartDate, CurrentIntegral = @CurrentIntegral, IsAutoRecharge = @IsAutoRecharge, GuardLine = @GuardLine WHERE CompanyID = @CompanyID  ";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usInsertUpdateCompany");
            SqlCommand sqlCmd = new SqlCommand(sqlUpdate);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = pCompany.CompanyID;
            sqlCmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = pCompany.CompanyName;
            sqlCmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = pCompany.Address;
            sqlCmd.Parameters.Add("@Contact", SqlDbType.VarChar).Value = pCompany.Contact;
            sqlCmd.Parameters.Add("@ContactPerson", SqlDbType.VarChar).Value = pCompany.ContactPerson;
            sqlCmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = pCompany.Phone;
            sqlCmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = pCompany.Email;
             
            if (pCompany.StatisticalEndDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@StatisticalEndDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@StatisticalEndDate", SqlDbType.DateTime).Value = pCompany.StatisticalEndDate;
            }

            if (pCompany.StatisticalStartDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@StatisticalStartDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@StatisticalStartDate", SqlDbType.DateTime).Value = pCompany.StatisticalStartDate;
            }
            sqlCmd.Parameters.Add("@CurrentIntegral", SqlDbType.Int).Value = pCompany.CurrentIntegral;
            sqlCmd.Parameters.Add("@IsAutoRecharge", SqlDbType.Int).Value = pCompany.IsAutoRecharge;
            sqlCmd.Parameters.Add("@GuardLine", SqlDbType.Int).Value = pCompany.GuardLine;

            try
            {
                // open the connection
                conn.Open();

                //execute the update command
                sqlCmd.ExecuteNonQuery();

                //close the connection
                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
        }

        /// <summary>
        ///Description   : create a new Company record in the db
        ///Pre-condition : none
        ///Post-condition: none
        ///Return        : None.
        /// </summary>
        public void InsertCompany(Company pCompany)
        {
            string sqlInsert = " INSERT INTO Company (CompanyName,Address,Contact,ContactPerson,Phone,Email,StatisticalEndDate,StatisticalStartDate,CurrentIntegral,IsAutoRecharge,GuardLine) VALUES (  @CompanyName, @Address, @Contact, @ContactPerson, @Phone, @Email, @StatisticalEndDate, @StatisticalStartDate, @CurrentIntegral, @IsAutoRecharge, @GuardLine) ";
            SqlConnection conn = new SqlConnection(ConnectionString);

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usInsertCompany");
            SqlCommand sqlCmd = new SqlCommand(sqlInsert);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

           
            sqlCmd.Parameters.Add("@CompanyName", SqlDbType.VarChar).Value = pCompany.CompanyName;
            sqlCmd.Parameters.Add("@Address", SqlDbType.VarChar).Value = pCompany.Address;
            sqlCmd.Parameters.Add("@Contact", SqlDbType.VarChar).Value = pCompany.Contact;
            sqlCmd.Parameters.Add("@ContactPerson", SqlDbType.VarChar).Value = pCompany.ContactPerson;
            sqlCmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = pCompany.Phone;
            sqlCmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = pCompany.Email;
            
            if (pCompany.StatisticalEndDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@StatisticalEndDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@StatisticalEndDate", SqlDbType.DateTime).Value = pCompany.StatisticalEndDate;
            }

            if (pCompany.StatisticalStartDate == DateTime.MinValue)
            {
                sqlCmd.Parameters.Add("@StatisticalStartDate", SqlDbType.DateTime).Value = System.Data.SqlTypes.SqlDateTime.MinValue;
            }
            else
            {
                sqlCmd.Parameters.Add("@StatisticalStartDate", SqlDbType.DateTime).Value = pCompany.StatisticalStartDate;
            }


            sqlCmd.Parameters.Add("@CurrentIntegral", SqlDbType.Int).Value = pCompany.CurrentIntegral;
            sqlCmd.Parameters.Add("@IsAutoRecharge", SqlDbType.Int).Value = pCompany.IsAutoRecharge;
            sqlCmd.Parameters.Add("@GuardLine", SqlDbType.Int).Value = pCompany.GuardLine;

            try
            {
                // open the connection and run the insert command
                conn.Open();
                sqlCmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
        }

        /// <summary>
        ///Description   : Deletes a Company record from the db
        ///Pre-condition : record has to exist in db
        ///Post-condition: none
        ///Return        : true - if record was deleted successfully.
        ///				   false - if record was not deleted;
        /// </summary>
        public bool DeleteCompany(int pCompanyID)
        {
            string sqlDelete = " delete from Company WHERE CompanyID = @CompanyID  ";
            SqlConnection conn = new SqlConnection(ConnectionString);
            bool pboolResult = false;

            // create the command for the stored procedure
            //SqlCommand sqlCmd = new SqlCommand("usDeleteCompany");
            SqlCommand sqlCmd = new SqlCommand(sqlDelete);
            sqlCmd.Connection = conn;
            //sqlCmd.CommandType = CommandType.StoredProcedure;

            sqlCmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = pCompanyID;

            try
            {
                // open the connection and create the DataReader
                conn.Open();
                sqlCmd.ExecuteNonQuery();

                conn.Close();
                pboolResult = true;
            }
            catch (SqlException pEx)
            {
                conn.Close();
                LastErrorMessage = pEx.Message;
                LogErrorMessage();
                throw pEx;
            }
            return pboolResult;
        }
    }
}